﻿using DocumentFormat.OpenXml.Lite.Core.Office;
using DocumentFormat.OpenXml.Lite.Shared;
using DocumentFormat.OpenXml.Lite.SpreadsheetML.Excel.Base;
using DocumentFormat.OpenXml.Lite.SpreadsheetML.Excel.RunProperties;
using DocumentFormat.OpenXml.Lite.SpreadsheetML.Excel.Table;
using DocumentFormat.OpenXml.Lite.SpreadsheetML.Excel.Types;
using DocumentFormat.OpenXml.Lite.SpreadsheetML.Excel.WorksheetElements;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Samples.Excel
{
    class Tables
    {
        public static void Create()
        {
            Workbook wb = DocumentManager.Instance.CreateSpreadsheet();

            Worksheet sheet1 = wb.CreateWorksheet("Sheet1", null);

            CreateHeaderCell(1, 1, sheet1, "Browser");
            CreateHeaderCell(1, 2, sheet1, "Company");
            CreateHeaderCell(1, 3, sheet1, "Cost");
            CreateHeaderCell(1, 4, sheet1, "Engine");

            AddString(2, 1, sheet1, "Chrome");
            AddString(2, 2, sheet1, "Google");
            AddString(2, 3, sheet1, "Free");
            AddString(2, 4, sheet1, "Webkit");

            AddString(3, 1, sheet1, "IE");
            AddString(3, 2, sheet1, "Microsoft");
            AddString(3, 3, sheet1, "Free");
            AddString(3, 4, sheet1, "Trident");

            AddString(4, 1, sheet1, "Firefox");
            AddString(4, 2, sheet1, "Mozilla");
            AddString(4, 3, sheet1, "Free");
            AddString(4, 4, sheet1, "Gecko");

            AddString(5, 1, sheet1, "Opera");
            AddString(5, 2, sheet1, "Opera Software");
            AddString(5, 3, sheet1, "Free");
            AddString(5, 4, sheet1, "Presto");

            AddString(6, 1, sheet1, "Safari");
            AddString(6, 2, sheet1, "Apple");
            AddString(6, 3, sheet1, "Free");
            AddString(6, 4, sheet1, "Webkit");

            //NOTE: displayname cannot contain spaces
            Table table = wb.CreateTableFor(sheet1, "FirstTable", "A1:D6");
            table.AutoFilter = new AutoFilter() { Reference = "A1:D6" };
            table.AddColumn("Browser");
            table.AddColumn("Company");
            table.AddColumn("Cost");
            table.AddColumn("Engine");

            wb.Save(@"samples\table.xlsx");

            Workbook parsed = null;
            if (DocumentManager.Instance.OpenExcelDocument(@"samples\table.xlsx", out parsed))
            {
                Console.WriteLine("Sample created and parsed succesfully!");
            }
            Console.ReadLine();
        }

        private static void AddString(int row, int column, Worksheet sheet, string text)
        {
            Cell c = sheet.CreateCell(row, column);
            c.CellType = ST_CellType.String;
            c.AddSimpleText(text);
        }

        private static void CreateHeaderCell(int row, int column, Worksheet sheet, string text)
        {
            Cell c = sheet.CreateCell(row, column);
            c.CellType = ST_CellType.InlineString;
            RichTextRun r = new RichTextRun()
            {
                Text = new SimpleText(text)
            };
            r.AddProperty(new Bold());
            c.InlineRichText = new InlineRichText();
            c.InlineRichText.Runs.Add(r);
        }
    }
}
